﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;

using LoomDataEmulator.Data;

namespace LoomDataEmulator.Data
{
    public class DatabaseSchemaCreator
    {
        public static void CreateTestDatabaseEnvironment(string databaseName, string serverName)
        {
            var sqlHandler = new SQLHandler();

            Assembly assembly;
            StreamReader sqlStreamReader, sqlCreateDatabaseReader;

            try
            {
                assembly = Assembly.GetAssembly(typeof(LoomDataEmulator.Data.SQLHandler));
                sqlStreamReader = new StreamReader(assembly.GetManifestResourceStream("LoomDataEmulator.Data.Scripts.SQLQuery_CreateLayouts.sql"));
                sqlCreateDatabaseReader = new StreamReader(assembly.GetManifestResourceStream("LoomDataEmulator.Data.Scripts.SQLQuery_CreateDatabase.sql"));
            }
            catch (Exception)
            {
                
                throw;
            }
            
            sqlHandler.DataBaseName = "master";
            sqlHandler.DataSource = serverName;
            sqlHandler.IntegratedSecurity = true;

            SqlCommand sqlCommand = null;

            try
            {
                sqlHandler.Connect();

                if(sqlHandler.SqlConnection.State == ConnectionState.Open)
                {
                    
                    sqlCommand = sqlHandler.SqlConnection.CreateCommand();
                    sqlCommand.CommandText = String.Format(sqlCreateDatabaseReader.ReadToEnd(), databaseName);
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = String.Format(sqlStreamReader.ReadToEnd(), databaseName);
                    sqlCommand.ExecuteNonQuery();
                    
                    sqlHandler.SqlConnection.ChangeDatabase("master");
                    sqlHandler.SqlConnection.Close();
                }

            }
            catch (Exception e)
            {
                    
                throw e;
            }
            
        }
        public static void DeleteTestDatabaseEnvironment(string databaseName, string serverName)
        {
            var sqlHandler = new SQLHandler();

            Assembly assembly;
            StreamReader sqlStreamReader, sqlCreateDatabaseReader;

            try
            {
                assembly = Assembly.GetAssembly(typeof(LoomDataEmulator.Data.SQLHandler));
                sqlStreamReader = new StreamReader(assembly.GetManifestResourceStream("LoomDataEmulator.Data.Scripts.SQLQuery_DeleteLayouts.sql"));
            }
            catch (Exception)
            {

                throw;
            }

            sqlHandler.DataBaseName = "master";
            sqlHandler.DataSource = serverName;
            sqlHandler.IntegratedSecurity = true;

            SqlCommand sqlCommand = null;

            try
            {
                sqlHandler.Connect();

                if (sqlHandler.SqlConnection.State == ConnectionState.Open)
                {
                    sqlCommand = sqlHandler.SqlConnection.CreateCommand();
                    sqlCommand.CommandText = String.Format(sqlStreamReader.ReadToEnd(), databaseName);
                    sqlCommand.ExecuteNonQuery();

                    sqlHandler.SqlConnection.Close();
                }

            }
            catch (Exception e)
            {

                throw e;
            }
        }
    }
}
